Skip Headers

Oracle® Database Programmer's Guide to the Oracle Precompilers 10g Release 2 (10.2) Part Number B14354-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# E Migrating to Oracle Database Version 7

Oracle database version 7 conforms fully to the new ANSI/ISO SQL standard. As a result, Oracle database version 7 and Oracle Version 6 behave differently in a few areas. By pointing out those areas, this appendix helps you migrate your application programs to Oracle database version 7.

## E.1 Fetching Nulls

When `MODE=ORACLE` (the default), if your program fetches a null into a host variable that lacks an indicator variable, Oracle Version 6 generates no error; however, Oracle database version 7 generates the following error:

    ORA-01405: fetched column value is NULL

Therefore, to migrate the program from Oracle Version 6 to Oracle database version 7, you may:

- Re-precompile the program, specifying DBMS=V6 on the command line, which preserves the Oracle Version 6 behavior,

- Revise the program by associating an indicator variable with every host variable into which nulls might be fetched, or

- Specify `UNSAFE_NULL=YES` with `MODE=ORACLE` and `DBMS=V7` to simply disable the `ORA-01405` message.

When `DBMS=V6`, regardless of the `MODE` setting, the following changes go into effect:

- If you fetch a null into a host variable that lacks an indicator variable, Oracle database version 7 generates no error (instead of generating error `ORA-01405`).

- If you fetch a truncated value into a host variable that lacks an indicator variable, Oracle database version 7 generates error `ORA-01406` (instead of generating no error).

- Oracle database version 7 treats the following items like variable-length character values (instead of fixed-length character values):

  - String literals

  - Local `CHAR` variables in a PL/SQL block

  - The return value of the function `USER`

- If you process a multirow query that calls a SQL group function such as `AVG` or `COUNT`, the function is called at `OPEN` time (instead of at `FETCH` time).

When `DBMS=V6`, if you fetch nulls into a host array that lacks an indicator array, Oracle database version 7 continues processing. So, always use an indicator array; otherwise, you cannot find the nulls. (To Learn how to find nulls and truncated values, see "Using Indicator Variables".)

### E.1.1 Fetching Truncated Values

When `MODE=ORACLE`, if your program fetches a truncated value into a host variable that lacks an indicator variable, Oracle database version 7 generates no error; however, Oracle Version 6 generates the following error:

    ORA-01406: fetched column value was truncated

Therefore, to migrate the program from Oracle Version 6 to Oracle database version 7, you must either

- re-precompile the program, specifying `DBMS=V6` on the command line, which preserves the Oracle Version 6 behavior, or

- revise the program by associating an indicator variable with every host variable into which truncated values might be fetched

When `DBMS=V6`, if you fetch a truncated value into a host array that lacks an indicator array, Oracle database version 7 stops processing and sets `SQLERRD(3)` in the SQLCA to the number of rows processed. No other information is available. So, always use an indicator array; otherwise, you cannot find the truncated values and their original lengths.

### E.1.2 Handling Character Strings

Some application programs expect variable-length character strings (of type `VARCHAR2`, for example). However, by default, Oracle database version 7 uses fixed-length character strings of type `CHAR`. This affects string-comparison semantics. For details, see the next section.

If your program expects variable-length character strings, specify `DBMS=V6` on the precompiler command line. Among other things, this preserves Oracle Version 6 string-comparison semantics.

### E.1.3 Using VARCHAR2 versus CHAR

The `VARCHAR2` and `CHAR` datatypes differ in subtle but significant ways. `CHAR` semantics have changed slightly to comply with the current ANSI/ISO SQL standard. The changes come into play when you compare, insert, update, select, or fetch character values.

### E.1.4 On Input

When `MODE=ANSI`, if both values being compared in a SQL statement belong to type `CHAR`, blank-padding semantics are used. That is, before comparing character values of unequal length, Oracle blank-pads the shorter value to the length of the longer value. For example, if `ENAME` is a `CHAR` database column and emp_name is a `CHAR` host variable (by default or datatype equivalencing), the following search condition is `TRUE` when the column value "BELL" and the host value "BELL####" (with four trailing spaces) are compared:

    ... WHERE ENAME = :emp_name;

When `MODE={ANSI14|ANSI13|ORACLE}`, if either or both values in a comparison belong to type `VARCHAR2`, non-blank-padding semantics are used. That is, when comparing character values of unequal length, ORACLE makes no adjustments and uses the exact lengths. For example, if `JOB` is a `CHAR` column and job_title is a `VARCHAR2` host variable, the following search condition is `FALSE` when the column value "CLERK" and the host value "CLERK###" (with three trailing spaces) are compared:

    ... WHERE JOB = :job_title;

When you insert a character value into a `CHAR` database column, if the value is shorter than the defined width of the column, Oracle blank-pads the value to the defined width. As a result, information about trailing blanks is lost. If the character value is longer than the defined width of the `CHAR` column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks.

When you insert a character value into a `VARCHAR2` database column, if the value is shorter than the defined width of the column, Oracle does not blank-pad the value. Nor does Oracle strip trailing blanks. Character values are stored intact, so no information is lost. If the character value is longer than the defined width of the `VARCHAR2` column, Oracle generates an error. Oracle neither truncates the value nor tries to trim trailing blanks.

The same rules apply when updating.

### E.1.5 On Output

When a column value is selected into a `CHAR` host variable, if the value is shorter than the declared length of the variable, Oracle blank-pads the value to the declared length. For example, if emp_name is a `CHAR(15)` host variable (by default or variable equivalencing), and 10-byte column value is selected into it, Oracle appends 5 blank characters to it. If the column value is longer than the declared length of the `CHAR` host variable, Oracle truncates the value, stores it, and generates a warning.

When a column value is selected into a `VARCHAR2` host variable, if the value is shorter than the declared length of the variable, Oracle does not blank-pad the value; nor does Oracle strip trailing blanks. If the column value is longer than the declared length of the `VARCHAR2` host variable, Oracle truncates the value, stores it, and generates a warning.

The same rules apply when fetching.